﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.Office.Interop.PowerPoint;
using System;
using System.Drawing;
using System.Linq;
using PpChartType = Microsoft.Office.Core.XlChartType;

namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateCashFlowDiff
    {
        public static void Write(int year, int month, int comYear, int comMonth)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            using (var db = new FinancialReportContext())
            {
                //先准备数据
                var cpldata = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Year == year && x.Month == month && x.Restaurant == "总表 ");
                var compldata = db.PLDatas.FirstOrDefault(x => x.IsPlan == false && x.Year == comYear && x.Month == comMonth && x.Restaurant == "总表");
                var cfdiff = (cpldata.债前现金流 / cpldata.产品净收入 - compldata.债前现金流 / compldata.产品净收入);
                var diffamount = (cfdiff * compldata.产品净收入).ToString("n");
                //写入标题           
                Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 30, 0, 500, 30);
                titleBox.TextFrame.TextRange.Text = $"VS.{year}.{month.ToString("00")}债前现金流{cfdiff.ToString("p")}，相对金额是{diffamount}";
                string[] arrDiff = new string[] { "食品成本", "包装成本", "运费", "员工薪资", "管理组薪资", "薪资税", "广告", "促销", "外部服务", "制服", "营运物料", "维护保养", "公共事业", "杂费", "租金", "服务费","折旧", "GA", "非产品利润", "再投资" };


                //写入平台差异图表                
                Chart chart = currentslide.Shapes.AddChart2(
                Style: -1,
                    Type: PpChartType.xlColumnClustered,
                    Left: 0,
                    Top: 31,
                    Width: currentslide.Master.Width,
                    Height: currentslide.Master.Height - 31,
                    NewLayout: true).Chart;
                // 设置图表的数据
                // 获取图表的数据源工作表  
                var worksheet = chart.ChartData.Workbook.Worksheets[1];
                // 清除之前的数据
                worksheet.UsedRange.Clear();
                // 修改工作表中的数据
                worksheet.Cells[1, 1].Value = null;
                worksheet.Cells[1, 2].Value = "百分比";
                worksheet.Cells[1, 3].Value = "绝对值";
                worksheet.Cells[2, 1].Value = comYear.ToString();
                worksheet.Cells[2, 2].Value = (compldata.债前现金流 / compldata.产品净收入).ToString("p");
                int row = 3;
                //输出绝对值
                foreach (var name in arrDiff)
                {
                    decimal ctemp, comtemp, percentageDiff;

                    if (name == "GA")
                    {
                        ctemp = (decimal)cpldata.GetType().GetProperty("法律费用").GetValue(cpldata, null)+ (decimal)cpldata.GetType().GetProperty("其他费用").GetValue(cpldata, null);
                        comtemp = (decimal)compldata.GetType().GetProperty("法律费用").GetValue(compldata, null)+ (decimal)compldata.GetType().GetProperty("其他费用").GetValue(compldata, null);
                        percentageDiff = ctemp / cpldata.产品净收入 - comtemp / compldata.产品净收入;
                    }
                    else
                    {
                        ctemp = (decimal)cpldata.GetType().GetProperty(name).GetValue(cpldata, null);
                        comtemp = (decimal)compldata.GetType().GetProperty(name).GetValue(compldata, null);
                        percentageDiff = ctemp / cpldata.产品净收入 - comtemp / compldata.产品净收入;
                    }
                    if (Math.Abs(percentageDiff) <= 0.001m)
                    {
                        continue;
                    }
                    if (name =="非产品利润")
                    {
                        percentageDiff = -percentageDiff;
                    }
                    worksheet.Cells[row, 1].Value = name;
                    worksheet.Cells[row, 3].Value = (percentageDiff * compldata.产品净收入 / 10000).ToString("n");
                    worksheet.Cells[row, 4].Value = percentageDiff.ToString("p");
                    row++;
                }
                worksheet.Cells[row, 1].Value = year.ToString();
                worksheet.Cells[row, 2].Value = (cpldata.债前现金流 / cpldata.产品净收入).ToString("p");
                // 设置图表的数据范围            
                string rangeAddress = "='Sheet1'!" + worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[row, 3]].Address;
                chart.SetSourceData(rangeAddress);
                chart.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(99,184,255));
                chart.SeriesCollection(1).Points(chart.SeriesCollection(1).Points.Count).Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(99,184,255));
                for (int i = row - 2; i >= 2; i--)
                {
                    var flag = (decimal)worksheet.cells[i+1, 3].Value;
                    if (flag > 0)
                    {
                        chart.SeriesCollection(2).Points(i).Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(178, 34, 34));
                    }
                    else
                    {
                        chart.SeriesCollection(2).Points(i).Format.Fill.ForeColor.RGB = ColorTranslator.ToOle(Color.FromArgb(0, 139, 0));
                    }
                }
                // 保存并关闭Excel工作簿
                chart.ChartData.Workbook.Close(true);
                //添加图表标题
                chart.HasTitle = true;
                chart.ChartTitle.Text = "MTD债前现金流差异";
                // 添加图例
                chart.HasLegend = true;
                // 添加数据标签
                chart.ApplyDataLabels();                
                chart.SeriesCollection(2).AxisGroup = XlAxisGroup.xlSecondary;
                // 取消横向网格线
                chart.Axes(XlAxisType.xlValue).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlValue).HasMinorGridlines = false;
                // 取消纵向网格线
                chart.Axes(XlAxisType.xlCategory).HasMajorGridlines = false;
                chart.Axes(XlAxisType.xlCategory).HasMinorGridlines = false;
                //数据绘图区填充颜色
                chart.PlotArea.Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(233, 233, 238).ToArgb();
                //隐藏主次纵坐标轴
                chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlSecondary).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionNone;
                //设置横坐标的标签位置
                chart.Axes(XlAxisType.xlCategory).TickLabelPosition = XlTickLabelPosition.xlTickLabelPositionLow;
            }
        }
    }
}
